package vt.transit.server.data.access;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import vt.transit.server.data.BusStop;
import vt.transit.server.data.BusStopCrime;
import vt.transit.server.data.schema.BusColumns;
import vt.transit.server.data.schema.CrimeAggregateColumns;

public class BusStopService {

	private static final Logger logger = Logger.getLogger(BusStopService.class);
	
	public static BusStop getClosestBusStop(Connection conn, double lat, double lon) {
		
		return null;
	}
	
	public static List<BusStop> getAllBusStopsNearby(Connection conn, double lat, double lon, double distance) {
		String query = 
			"SELECT bus.gid, bus.name, bus.stop_id, bus.routes, ST_AsText(bus.location) as geom " +
			"FROM bus " +
			"WHERE ST_DWithin(bus.location::geography, ST_GeographyFromText('POINT(" + lon + " " + lat + ")'), " + distance + ") ";
		
		List<BusStop> busStops = new ArrayList<BusStop>();

		try {
			Statement st = conn.createStatement();
			ResultSet rs = null;
			try {
				logger.info("Executing query:\n" + query);
				rs = st.executeQuery(query);
				while (rs.next()) {
					BusStop busStop = createBusStop(rs);								
					busStops.add(busStop);
				}

			} finally {
				if (rs != null) {
					rs.close();
				}

				st.close();
			}
		} catch (Exception e) {
			logger.error("Exception while running query", e);
		} 

		logger.info(busStops.size());
		return busStops;

	}
	
	public static List<BusStopCrime> getBusStopsAndCrimeNearby(Connection conn, double lat, double lon, int count) {
		String query =
			"SELECT bus.gid, bus.name, bus.stop_id, bus.routes, ST_AsText(bus.location) as geom, bus_crime_aggregate.aggregate_array, bus_crime_aggregate.total, " +
			"ST_DISTANCE(ST_GeographyFromText('SRID=4326;POINT(" + lon + " " + lat + ")'), location) as dist " +
			"FROM bus " +
			"LEFT OUTER JOIN bus_crime_aggregate " +
			"ON bus.stop_id = bus_crime_aggregate.stop_id " +
			"ORDER BY dist asc " +
			"LIMIT " + count;
		
		List<BusStopCrime> busStopCrimes = new ArrayList<BusStopCrime>();

		try {
			Statement st = conn.createStatement();
			ResultSet rs = null;
			try {
				logger.info("Executing query:\n" + query);
				rs = st.executeQuery(query);
				while (rs.next()) {
					BusStopCrime busCrime = createBusStopCrime(rs);								
					busStopCrimes.add(busCrime);
				}

			} finally {
				if (rs != null) {
					rs.close();
				}

				st.close();
			}
		} catch (Exception e) {
			logger.error("Exception while running query", e);
		} 

		logger.info(busStopCrimes.size());
		return busStopCrimes;
	}
	
	private static BusStop createBusStop(ResultSet rs) throws SQLException {
		BusStop busStop = new BusStop();
		busStop.setGid(rs.getInt(BusColumns.gid));
		busStop.setName(rs.getString(BusColumns.name));
		busStop.setStop_id(rs.getInt(BusColumns.stop_id));
		busStop.setLocation(rs.getString("geom"));
		String routes = rs.getString(BusColumns.routes);
		
		// strip out the beginning { and end }
		routes = routes.substring(1, routes.length()-1);		
		String[] routesArray = routes.split(",");

		for (String route : routesArray) {
			busStop.addRoute(route);
		}
		
		return busStop;
	}
	
	private static BusStopCrime createBusStopCrime(ResultSet rs) throws SQLException {
		BusStopCrime busCrime = new BusStopCrime();
		BusStop busStop = createBusStop(rs);
		
		busCrime.setBusStop(busStop);
		busCrime.setCrimeAggregate(rs.getString(CrimeAggregateColumns.aggregate_array));
		busCrime.setTotal(rs.getInt(CrimeAggregateColumns.total));
		busCrime.setDistance(rs.getDouble("dist"));
		
		return busCrime;
	}
}
